package itesm.coler.data;

import itesm.coler.data.DAOUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.StringTokenizer;

import javax.sql.*;

import itesm.coler.models.User;

public class MySQLUserDAO implements UserDAO {
	Connection connection = null;
	
	public MySQLUserDAO(){
		
		// MySQLUserDAO implementation of the UserDAO interface. 
		// This class can contain all MySQL specific code and SQL statements. 
		// 
		// The client is thus shielded from knowing 
		// these implementation details.
	}
    
    public Connection getConnection(){
		if (connection == null)
		    connection = MySQLDAOFactory.createConnection();
		return connection;
	}
	
	public void setConnection(Connection connection) {
		this.connection = connection;
	}
	
	 public User findUserByUserId(String userId) throws DAOException {
		 // Implement find a user here using supplied
		 // argument values as search criteria
		 // Return a Transfer Object if found,
		 // return null on error or if not found
        PreparedStatement findUser = null;
        ResultSet results= null;
        User user = null;
		try{
				  connection = getConnection();
				  if (connection!=null){
					  findUser = connection.prepareStatement("select * from user where userid = ?");
					  findUser.setString(1,userId);
					  results = findUser.executeQuery();
					  if (results.next()) {
						  user = mapResultSetToObject(results);						  			  	
					  }
				  }
		}catch(SQLException e){
			throw new DAOException(e);
		} finally {
			DAOUtil.close(connection,findUser,results);
		}
		return user;
	 }
	 
    public User findUserByUserName(String userName) throws DAOException {
		 // Implement find a user here using supplied
		 // argument values as search criteria
		 // Return a Transfer Object if found,
		 // return null on error or if not found
        PreparedStatement find = null;
        ResultSet results= null;
        User user = null;
		try{
			      connection = getConnection();
				  if (connection!=null){
					  find = connection.prepareStatement("select * from user where username = ?");
					  find.setString(1,userName);
					  results = find.executeQuery();
					  if (results.next()) {
						  user = mapResultSetToObject(results);						  			  	
					  }
				  }
		}catch(SQLException e){
			throw new DAOException(e);
		} finally {
			DAOUtil.close(connection,find,results);
		}
		return user;
	 }
    
    public User find(String username, String password) throws DAOException {
   	 // Implement find a user here using supplied
		 // argument values as search criteria
		 // Return a Transfer Object if found,
		 // return null on error or if not found
       PreparedStatement find = null;
       ResultSet results= null;
       User user = null;
		try{
			      connection = getConnection();
				  if (connection!=null){
					  find = connection.prepareStatement("select * from user where username = ? and password = ?");
					  find.setString(1,username);
					  find.setString(2,password);
					  results = find.executeQuery();
					  if (results.next()) {
						  user = mapResultSetToObject(results);						  			  	
					  }
				  }
		}catch(SQLException e){
			throw new DAOException(e);
		} finally {
			DAOUtil.close(connection,find,results);
		}
		return user;
    }

    public ArrayList<User> findAll() throws DAOException {
	    // implement search users here using the
	    // supplied criteria.
	    // Alternatively, implement to return a Collection 
	    // of Transfer Objects.
         PreparedStatement findUser = null;
         ResultSet results= null;
		 ArrayList<User> userList = null;
		 try {
		       connection = getConnection();
		       findUser = connection.prepareStatement("SELECT * FROM user");
		       synchronized(findUser) {
		       // obtain list of users	  
		    	  results = findUser.executeQuery();
		       // get rows data{
		    	  userList = new ArrayList<User>();
		         while (results.next()) {
		        	 User user = mapResultSetToObject(results);		
		        	 userList.add(user);	   
		         }
		       }
		   } catch (SQLException e){
			   throw new DAOException(e);
		   } finally {
				DAOUtil.close(connection,findUser,results);
		   }
		  return userList;
	  }
    
    
    public ArrayList<User> findAllByType(String type) throws DAOException {
	    // implement search users here using the
	    // supplied criteria.
	    // Alternatively, implement to return a Collection 
	    // of Transfer Objects.
         PreparedStatement findUsersByType= null;
         ResultSet results= null;
		 ArrayList<User> userList = null;
		 try {
		       connection = getConnection();
		       findUsersByType = connection.prepareStatement("SELECT * FROM user where usertype = ?");
		       synchronized(findUsersByType) {
		    	   findUsersByType.setString(1,type);
		       // obtain list of users	  
		    	  results = findUsersByType.executeQuery();
		       // get rows data{
		    	  userList = new ArrayList<User>();
		         while (results.next()) {
		        	 User user = mapResultSetToObject(results);		
		        	 userList.add(user);	   
		         }
		       }
		   } catch (SQLException e){
			   throw new DAOException(e);
		   } finally {
				DAOUtil.close(connection,findUsersByType,results);
		   }
		  return userList;
	  }
    
    public ArrayList<User> findAllByTeam(int teamId) throws DAOException {
	    // implement search users here using the
	    // supplied criteria.
	    // Alternatively, implement to return a Collection 
	    // of Transfer Objects.
         PreparedStatement findUsersByTeam = null;
         ResultSet results= null;
		 ArrayList<User> userList = null;
		 try {
		       connection = getConnection();
		       findUsersByTeam = connection.prepareStatement("SELECT * FROM user where teamid = ?");
		       synchronized(findUsersByTeam) {
		    	   findUsersByTeam.setInt(1,teamId);
		       // obtain list of users	  
		    	  results = findUsersByTeam.executeQuery();
		       // get rows data{
		    	  userList = new ArrayList<User>();
		         while (results.next()) {
		        	 User user = mapResultSetToObject(results);		
		        	 userList.add(user);	   
		         }
		       }
		   } catch (SQLException e){
			   throw new DAOException(e);
		   } finally {
				DAOUtil.close(connection,findUsersByTeam,results);
		   }
		  return userList;
	  }
    
    public ArrayList<User> findAllByCourse(String courseId) throws DAOException {
		    // implement search users here using the
		    // supplied criteria.
		    // Alternatively, implement to return a Collection 
		    // of Transfer Objects.
	         PreparedStatement findUsersByCourse = null;
	         ResultSet results= null;
			 ArrayList<User> userList = null;
			 try {
			       connection = getConnection();
			       findUsersByCourse = connection.prepareStatement("SELECT * FROM user natural join course where courseid = ?");
			       synchronized(findUsersByCourse) {
			    	   findUsersByCourse.setString(1,courseId);
			       // obtain list of users	  
			    	  results = findUsersByCourse.executeQuery();
			       // get rows data{
			    	  userList = new ArrayList<User>();
			         while (results.next()) {
			        	 User user = mapResultSetToObject(results);		
			        	 userList.add(user);	   
			         }
			       }
			   } catch (SQLException e){
				   throw new DAOException(e);
			   } finally {
					DAOUtil.close(connection,findUsersByCourse,results);
			   }
			  return userList;
		  }
     // The following methods can use
     // MySQLDAOFactory.createConnection() 
     // to get a connection as required
	 public int insertUser(User user) {
		    // Implement insert user here.
		    // Return 1 if the insert is correct.
		    // or a -1 on error	  

		    PreparedStatement insert = null;
		    int result=-1; //error
			 try { 
				    connection = getConnection();
				   // connection = ds.getConnection();
					insert = connection.prepareStatement("insert into user(username,firstname,middlename,lastname,gender,dateOfBirth, online,userType, active,password) values(?,?,?,?,?,?,?,?,?,?);");
					insert.setString(1, user.getUserName());
					insert.setString(2, user.getFirstName());
					insert.setString(3, user.getMiddleName());
					insert.setString(4, user.getLastName());
					insert.setString(5, user.getGender()+"");
					if(user.getDateOfBirth()!=null){
					insert.setDate(6,DAOUtil.toSqlDate(user.getDateOfBirth()));
					}
					insert.setInt(7, 0); //not online
					insert.setString(8, user.getUserType());
					insert.setInt(9, 1); //active
					insert.setString(10, user.getPassword());
					result = insert.executeUpdate();
					insert.close();
					connection.close();
			} catch (SQLException e) {
				  throw new DAOException(e);
	        } finally {
	              DAOUtil.close(connection, insert);
	        }
			return result;
	 }
	 
	 public int assignUserToCourse(String userId, String courseId ) throws DAOException {
		 // Implement insert user here.
		    // Return 0 if correct
		    // or a -1 on error	  
		    PreparedStatement insert  = null;
		    int result = -1;
			  try {
				  connection = getConnection();
				  insert = connection.prepareStatement("insert into usercourse(userId,courseid) values(?,?);");
				  insert.setString(1,userId);
				  insert.setString(2,courseId); 		 
				  result = insert.executeUpdate();
				  insert.close();
				  connection.close();
			} catch (SQLException e) {
				  throw new DAOException(e);
	        } finally {
	              DAOUtil.close(connection, insert);
	        }
			return result;
	 }
	 
	 public int assignUsersToCourse(String userIds, String courseId ) throws DAOException {
		    // implement assigned record here using data
		    // from the parameters
		    // Return 1 if correct, -1 on error
		    PreparedStatement insert  = null;
		    int result = -1;
			  try {
				  connection = getConnection();
				  insert = connection.prepareStatement("insert into usercourse(userId,courseid) values(?,?);");
				  StringTokenizer st = new StringTokenizer(userIds, ",");
				  while(st.hasMoreTokens()) {
					 String userId = st.nextToken(); 
				     insert.setString(1,userId); 
				     insert.setString(2,courseId);
				     result = insert.executeUpdate();			    
				  }
				  insert.close();
				  connection.close();
			  }catch (SQLException e) {
					  throw new DAOException(e);
		        } finally {
		              DAOUtil.close(connection, insert);
		        }  
				return result;
		 }
	 
		  
	 public boolean updateUser(User user) throws DAOException {
		    // implement update record here using data
		    // from the user   Object
		    // Return true on success, false on failure or
		    // error
		    PreparedStatement update  = null;
		    boolean result = true;
			  try {
				  connection = getConnection();
				  update = connection.prepareStatement("UPDATE user SET firstname = ?,middlename = ?,lastname = ?,gender = ?,dateOfBirth =?,active = ?, online = ?, userType = ?, password = ? WHERE username = ?");
				  update.setString(1, user.getFirstName());
				  update.setString(2, user.getMiddleName());
				  update.setString(3, user.getLastName());
				  update.setString(4, user.getGender()+"");
				  update.setDate(5, DAOUtil.toSqlDate(user.getDateOfBirth()));
				  update.setBoolean(6, user.isActive());  
				  update.setBoolean(7, user.isOnline());
				  update.setString(8, user.getUserType());
				  update.setString(9,user.getPassword());
				  update.setString(10, user.getUserName());
				  int affectedRows = update.executeUpdate();
				  if (affectedRows == 0) {
					    result = false;
		                throw new DAOException("Updating user failed, no rows affected.");
		            }
			} catch (SQLException e) {
				 result = false;
				 throw new DAOException(e);
		    } finally {
				DAOUtil.close(connection,update);
			}
			 return result;
		  }
	 public boolean deleteUser(String username) throws DAOException {
		  // Implement delete user here
		  // Return true on success, false on failure
		  PreparedStatement delete = null;
          boolean result = true;      
		  try {
			    connection = getConnection();
			    delete = connection.prepareStatement("delete from user where username = ?");
				delete.setString(1, username);
				int affectedRows = delete.executeUpdate();
			    if (affectedRows == 0) {
			    	    result = false;
		                throw new DAOException("Deleting user failed, no rows affected.");
		         }
			  } catch (SQLException e) {
				  result = false;
				  throw new DAOException(e);
			  } finally {
					DAOUtil.close(connection,delete);
			} 
            return result;
	} 
	 
	 
	  public boolean updateUserActiveStatus(String username, boolean status) throws DAOException {
		  PreparedStatement activeSatus = null;
		  boolean result = true;      
				  try {
					    connection = getConnection();
					    activeSatus = connection.prepareStatement("UPDATE user SET active = ? WHERE username = ?;");
						activeSatus.setBoolean(1, status);
						activeSatus.setString(2, username);
						result = activeSatus.execute();
						connection.close();
					  } catch (SQLException e) {
						  result = false;
						  throw new DAOException(e);
					  } finally {
							DAOUtil.close(connection,activeSatus);
					} 
		            return result;
		            
	  }
	  
	  public boolean updateUserOnlineStatus(String userName, boolean status) throws DAOException {
		  PreparedStatement activeStatus = null;
		     boolean result = true;      
				  try {
					    connection = getConnection();
					    activeStatus = connection.prepareStatement("UPDATE user SET online = ? WHERE username = ?;");
						activeStatus.setBoolean(1, status);
						activeStatus.setString(2, userName);
						result = activeStatus.execute();
						connection.close();
					  } catch (SQLException e) {
						  result = false;
						  throw new DAOException(e);
					  } finally {
							DAOUtil.close(connection,activeStatus);
					} 
		            return result;
      }
	  
		  public RowSet selectUsersRS() {
		    // implement search users here using the
		    // supplied criteria.
		    // Return a RowSet. 
			  return null;
		  }
		  
		  public Collection<User> selectUsersTO() {
			    // implement search users here using the
			    // supplied criteria.
			    // Return a RowSet. 
				  return null;
			  }

		 public User mapResultSetToObject(ResultSet results) throws SQLException {
			  System.out.println("Voy a mapear el objeto user");
			  User user = new User();
			  user.setUserId(results.getString("userid"));
			  user.setUserName(results.getString("username"));
			  user.setFirstName(results.getString("firstname"));
			  user.setMiddleName(results.getString("middlename"));
			  user.setLastName(results.getString("lastname"));
			  String gender = results.getString("gender");
			  if (gender!=null)
	        	     user.setGender(gender.charAt(0));
			  user.setGender(results.getString("gender").charAt(0));
			  if(results.getDate("dateOfBirth") != null){
				java.util.Date utilDate =  results.getDate("dateOfBirth");
			    SimpleDateFormat dia = new SimpleDateFormat("dd");
			    SimpleDateFormat mes = new SimpleDateFormat("MM");
			    SimpleDateFormat anio = new SimpleDateFormat("yyyy");
				user.setBirthDay(dia.format(utilDate));
				user.setBirthMonth(mes.format(utilDate));
				user.setBirthYear(anio.format(utilDate));
			  }
			  user.setOnline(results.getBoolean("online"));	  	
			  user.setUserType(results.getString("userType"));
			  user.setActive(results.getBoolean("active"));
			  user.setPassword(results.getString("password"));
			  Integer teamId = results.getInt("teamid");
			  System.out.println("teamId = "+teamId);
			  user.setTeamId(teamId.intValue());
	/*		  if ((teamId != null)&& (teamId !=0)) {
			         DAOFactory MySQLFactory = DAOFactory.getDAOFactory(DAOFactory.MySQL);
			  	     TeamDAO teamDAO = MySQLFactory.getTeamDAO(connection);
		             user.setTeam(teamDAO.findTeamById(teamId.intValue()));
			  } */
			  return user;
		 }
}
